# -*- coding:utf-8 -*-

'''
Created on Aug 24, 2011

@author: tanfeng
'''

import sys, os

from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from openpyxl.reader.excel import load_workbook 

from DBConnector import DBConnection

class SupplierImporter(object):
    
    def __init__(self):
        self.current_path = sys.path[0]
        self.excelFile = os.path.join('/Volumes/Leopard/Users/tanfeng/Work/workspace/autogo', 'files', 'supplier.xlsx')
        db = DBConnection()
        self.conn = db.conn
        self.cur = db.cur
        pass
    
    def importSupplier(self):
        wb = load_workbook(self.excelFile)
        ws = wb.get_active_sheet()
        ws_range = ws.calculate_dimension()
        for ws_row in ws.range(ws_range):
            cell = ws_row[0]
            # skipper header
            if cell.row == 1:
                continue
            
            brand = cell.value
            supplier_name = ws_row[1].value
            supplier_phone = ws_row[2].value
            supplier_address = ws_row[3].value
            print brand, supplier_name, supplier_phone, supplier_address
            sql = """insert into ag_supplier(brand_name,supplier_name,supplier_phone,supplier_address) values ('%s', '%s', '%s', '%s')""" % (brand, supplier_name, supplier_phone, supplier_address)
            self.cur.execute(sql)
            self.conn.commit()
    

s = SupplierImporter()
s.importSupplier()